package com.kuang.dao.bill;

import com.kuang.dao.BaseDao;
import com.kuang.pojo.Bill;
import com.mysql.jdbc.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class BillDaoImpl implements BillDao{

    //根据用户输入的值，新增订单表
    public int add(Connection connection, Bill bill) throws Exception {
        int updateNum = 0;
        PreparedStatement pstm = null;
        if (connection!=null){
            String sql = "insert into smbms_bill (billCode, productName, productDesc, productUnit, productCount, totalPrice, isPayment, providerId, createdBy, creationDate)\n" +
                    "values(?,?,?,?,?,?,?,?,?,?)";
            Object[] params = {bill.getBillCode(),bill.getProductName(),bill.getProductDesc(),bill.getProductUnit(),bill.getProductCount(),bill.getTotalPrice(),bill.getIsPayment(),bill.getProviderId(),bill.getCreatedBy(),bill.getCreationDate()};
            updateNum = BaseDao.execute(connection,pstm,sql,params);
            BaseDao.closeResource(null,pstm,null);
            System.out.println("dao------修改行数 " + updateNum);
        }
        return updateNum;
    }

    //根据用户输入的值，查询订单表
    public List<Bill> getBillList(Connection connection, String billName,int billId,int billIsPayment) throws Exception {
        List<Bill> billList = new ArrayList<Bill>();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        if (connection != null){
            StringBuffer sql = new StringBuffer();
            sql.append("SELECT b.*,p.proName AS providerName FROM smbms_bill b, smbms_provider p WHERE b.providerId = p.id");
            List<Object> list = new ArrayList<Object>();//用来暂存用户的输入

            if (!StringUtils.isNullOrEmpty(billName)){ //判断用户是否输入商品名称
                sql.append(" and b.productName like ?");
                list.add("%"+billName+"%");
            }
            if (billId>0) {//判断是否选择了供应商
                sql.append(" and p.id = ?");
                list.add(billId);
            }
            if (billIsPayment>0){//判断是否选择了是否付款
                sql.append(" and b.isPayment = ?");
                list.add(billIsPayment);
            }
            Object[] params = list.toArray();
            rs = BaseDao.execute(connection,pstm,rs,sql.toString(),params);
            System.out.println("当前的sql是---->" + sql.toString());
            System.out.println("==============");
            System.out.println(!StringUtils.isNullOrEmpty(billName));
            System.out.println("==============");
            while (rs.next()){
                Bill _bill = new Bill();//创建一个bill对象存储查询到的属性
                _bill.setId(rs.getInt("id"));
                _bill.setBillCode(rs.getString("billCode"));
                _bill.setProductName(rs.getString("productName"));
                _bill.setProductDesc(rs.getString("productDesc"));
                _bill.setProductUnit(rs.getString("productUnit"));
                _bill.setProductCount(rs.getBigDecimal("productCount"));
                _bill.setTotalPrice(rs.getBigDecimal("totalPrice"));
                _bill.setIsPayment(rs.getInt("isPayment"));
                _bill.setProviderId(rs.getInt("providerId"));
                _bill.setProviderName(rs.getString("providerName"));
                _bill.setCreationDate(rs.getTimestamp("creationDate"));
                _bill.setCreatedBy(rs.getInt("createdBy"));
                billList.add(_bill);
            }
            BaseDao.closeResource(null,pstm,rs);
        }
        return billList;
    }

    //根据订单id删除该订单
    public int deleteBillById(Connection connection, String delId) throws Exception {
        int delNum = 0;
        PreparedStatement pstm = null;
        if (connection != null){
            String sql = "delete from smbms_bill where id = ?";
            Object[] params = {delId};
            delNum = BaseDao.execute(connection,pstm,sql,params);
            BaseDao.closeResource(null,pstm,null);
        }
        return delNum;
    }

    //通过订单id得到该订单的所有信息（正确）
    public Bill getBillById(Connection connection, String id) throws Exception {
        Bill bill = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        if (connection != null){
            String sql = "select b.*,p.proName as providerName from smbms_bill b, smbms_provider p\n" +
                    "where b.providerId = p.id and b.id= ?";
            Object[] params = {id};
            rs = BaseDao.execute(connection,pstm,rs,sql,params);
            if (rs.next()){
                bill = new Bill();
                bill.setId(rs.getInt("id"));
                bill.setBillCode(rs.getString("billCode"));
                bill.setProductName(rs.getString("productName"));
                bill.setProductDesc(rs.getString("productDesc"));
                bill.setProductUnit(rs.getString("productUnit"));
                bill.setProductCount(rs.getBigDecimal("productCount"));
                bill.setTotalPrice(rs.getBigDecimal("totalPrice"));
                bill.setIsPayment(rs.getInt("isPayment"));
                bill.setProviderId(rs.getInt("providerId"));
                bill.setProviderName(rs.getString("providerName"));
                bill.setCreationDate(rs.getTimestamp("creationDate"));
                bill.setCreatedBy(rs.getInt("createdBy"));
            }
            BaseDao.closeResource(null,pstm,rs);
        }
        return bill;
    }

    //根据用户传递输入的值修改订单表
    public int modify(Connection connection, Bill bill) throws Exception {
        int modifyNum = 0;
        PreparedStatement pstm = null;
        if (connection != null){
            String sql = "update smbms_bill set productName = ?,productDesc = ?,productUnit = ?,productCount = ?,totalPrice = ?,isPayment = ?,providerId = ?,modifyBy = ?,modifyDate = ? where id = ?";
            Object[] params = {bill.getProductName(),bill.getProductDesc(),bill.getProductUnit(),bill.getProductCount(),bill.getTotalPrice(),bill.getIsPayment(),bill.getProviderId(),bill.getModifyBy(),bill.getModifyDate(),bill.getId()};
            modifyNum = BaseDao.execute(connection,pstm,sql,params);
            BaseDao.closeResource(null,pstm,null);
        }
        return modifyNum;
    }

    //通过供应商id得到该供应商总订单数(正确）
    public int getBillCountByProviderId(Connection connection, String providerId) throws Exception {
        PreparedStatement pstm = null;
        int billCount = 0;
        ResultSet rs = null;
        if (connection != null){
            String sql = "select count(1) as billCount From smbms_bill where providerId = ?";
            Object[] params = {providerId};
            rs = BaseDao.execute(connection,pstm,rs,sql,params);
            while (rs.next()){
                billCount = rs.getInt("billCount");
            }
            BaseDao.closeResource(null,pstm,rs);
        }
        return billCount;
    }
}
